package com.edu.dao.impl;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Random;

import javax.sql.rowset.CachedRowSet;

import com.edu.dao.IArticleDao;
import com.edu.entity.Articles;
import com.edu.entity.Types;
import com.edu.entity.Users;
import com.edu.util.DBUtil;
import com.edu.util.PageData;
//模糊查询

public class ArticleDaoImpl implements IArticleDao {
	/**
	 * 1.模糊查询，根据文章内容关键字查询11
	 * 
	 */
	@Override
	public List<Articles> queryArticlesByLike(String articlesLike) {
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		CachedRowSet crs = DBUtil.execQuery("select * from articles where articleTitle like ? or articleContent=?","%"+articlesLike+"%","%"+articlesLike+"%");
		try {
			while(crs.next()) {
				   int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   int typeId=crs.getInt(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(typeId);
				Articles articles1=  new Articles(articleId, articleTitle, articleContent, articleDate, articleVoice);
				list.add(articles1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	/**
	 * 2.模糊查询，根据文章内容Id查询
	 * 
	 */
	@Override
	public List<Articles> queryArticlesById(String articlesId) {
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		CachedRowSet crs = DBUtil.execQuery("select * from articles where articleId like ? or articleContent=?","%"+articlesId+"%","%"+articlesId+"%");
		try {
			while(crs.next()) {
				   int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String articleContent=crs.getString(3);
				   String articleDate=crs.getString(4);
				   String articleVoice=null;
				   Articles articles1=  new Articles(articleId, articleTitle, articleContent, articleDate, articleVoice);
				list.add(articles1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	
	/**
	 * 3.显示最新课程
	 * 
	 */

	@Override
	public List<Articles> queryTopArticles() {
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		CachedRowSet crs = DBUtil.execQuery("select * from articles  where articleStatus=1 order by articleDate desc limit 0,3");
		try {
			while(crs.next()) {
				 int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   int typeId=crs.getInt(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(typeId);
				   articles=  new Articles(articleId, articleTitle, users, articleContent, articleDate,types,articleVoice,articleRead,articleStatus,delDate,adminId);
				list.add(articles);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}

	@Override
	/**
	 * @author 陆亮
	 * 查询所有课程,注意字段是否有用
	 */
	public List<Articles> queryAllArticles() {
		String sql="select articleId,articleTitle,userAccount,articleContent,articleDate,typeContent,articleVoice,articleRead,articleStatus,delTime,adminId from articles a inner join types t on a.typeId=t.typeId";
		CachedRowSet crs=DBUtil.execQuery(sql);
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		try {
			while(crs.next()) {
				   int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   String type=crs.getString(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(type);
				   articles=  new Articles(articleId, articleTitle, users, articleContent, articleDate,types,articleVoice,articleRead,articleStatus,delDate,adminId);
				list.add(articles);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	/**
	 * @author 陆亮
	 * 注销文章
	 */
	@Override
	public boolean writeOutArticle(int articleId) {
		String sql="update articles set articleStatus=0 where articleId= ?";
		return DBUtil.executeUpdate(sql, articleId);
	}
	/**
	 * @author 陆亮
	 *恢复文章
	 */
	@Override
	public boolean writeInArticle(int articleId) {
		String sql="update articles set articleStatus=1 where articleId= ?";
		return DBUtil.executeUpdate(sql, articleId);
	}
	/**
	 * @author 陆亮
	 *分页查询或分页显示所有,后台显示
	 */
	@Override
	public PageData queryAllArticlesByTitle(int pageNo, int pageSize, String titleKey) {
		String sql="select articleId,articleTitle,userAccount,articleContent,articleDate,typeContent,articleVoice,articleRead,articleStatus,delTime,adminId from articles a inner join types t on a.typeId=t.typeId where articleTitle like  ?  LIMIT ?, ?";
		CachedRowSet crs=DBUtil.execQuery(sql,"%"+titleKey+"%",(pageNo-1)*pageSize,pageSize);
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		try {
			while(crs.next()) {
				   int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   String type=crs.getString(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(type);
				   articles=  new Articles(articleId, articleTitle, users, articleContent, articleDate,types,articleVoice,articleRead,articleStatus,delDate,adminId);
				list.add(articles);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql2="select count(articleTitle) from articles where articleTitle like ? ";
		CachedRowSet crs2=DBUtil.execQuery(sql2, "%"+titleKey+"%");
		int totalCount=0;
		try {
			while (crs2.next()) {
				totalCount=crs2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData pageData = new PageData(list,pageNo,pageSize,totalCount);
		return pageData;
	}
    
	/**
	 * @author 陆亮   查询今日新增文章数
	 */
	@Override
	public int queryNewArticle() {
		// TODO Auto-generated method stub
		String sql="select count(articleTitle) from articles where date(articleDate) = curdate()";
		CachedRowSet crs=DBUtil.execQuery(sql);
		int newArt=0;
		try {
			while (crs.next()) {
				newArt=crs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return newArt;
	}
    
	/**
	 * @author 陆亮   查询文章数
	 */
	@Override
	public int queryAllArticle() {
		String sql="select count(articleTitle) from articles";
		CachedRowSet crs=DBUtil.execQuery(sql);
		int allArt=0;
		try {
			while (crs.next()) {
				allArt=crs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return allArt;
	}
    /**
     * 邓学红编写 陆亮修改
     * 按阅读量显示前6  前台首页显示
     */
	@Override
	public List<Articles> queryArticles() {
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		CachedRowSet crs = DBUtil.execQuery("select  * from articles where articleStatus=1 ORDER BY articleRead DESC LIMIT 0,6");
		try {
			while(crs.next()) {
				   int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   int typeId=crs.getInt(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(typeId);
				   articles=  new Articles(articleId, articleTitle, users, articleContent, articleDate,types,articleVoice,articleRead,articleStatus,delDate,adminId);
				list.add(articles);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return list;
	}
	
	/**
	 * author 陆海
	 */
	//添加文章
	@Override
	public boolean addArticle(Articles articles) {
		// TODO Auto-generated method stub
		String sql1="insert into articles values(null,?,?,?,now(),?,null,0,1,null,null)";
		return DBUtil.executeUpdate(sql1,articles.getArticleTitle(),articles.getUsers().getUserAccount(),articles.getArticleContent(),articles.getTypes().getTypeId());
	}
	/**
	 * author 陆海
	 */
	//根据文章类型获取类型ID
	@Override
	public Types queryId(String typeContent) {
		Types types=null;
		CachedRowSet crs=DBUtil.execQuery("select typeId from types where typeContent=?", typeContent);
		try {
			while (crs.next()) {
				int typeId=crs.getInt(1);
				types=new Types(typeId);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return types;
	}
	
	@Override
	/**
	 * 陆亮  查询文章近7天新增数量
	 */
	public int[] query7DayAtricles() {
		//存储数据的数组
		int [] sum=new int[7];
		//数组下标索引
		int n=0;
		for (int i =sum.length; i >0; i--) {
			String sql="SELECT count(*) FROM articles where  to_days(now()) - to_days(articleDate) = ?";
			CachedRowSet crs=DBUtil.execQuery(sql, i);
			try {
				while(crs.next()) {
					 sum[n]=crs.getInt(1);
					 n++;
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return sum;
	}
	
    //根据ID查询文章
	@Override
	public Articles queryArticlesById(int atricleId) {
		// TODO Auto-generated method stub
		Articles articles=null;
		String sql= "select * FROM articles WHERE articleId=? and articleStatus=1";
		CachedRowSet crs = DBUtil.execQuery(sql, atricleId);
		try {
			while(crs.next()) {
				   int  articleId1 = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   int typeId=crs.getInt(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(typeId);
			       articles=new Articles(articleId1, articleTitle, users, articleContent, articleDate, types, articleVoice, articleRead, articleStatus, delDate, adminId);
				
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return articles;
	}
	
	/**
	 * @author 陆亮
	 * 查询每个类型的数量
	 * @return 
	 */
    public int[] queryTypeArticles() {
    	//查询类型数量
    	 int  countTypes=0;
    	CachedRowSet crs =DBUtil.execQuery("select count(typeContent) from types");
    	try {
			while(crs.next()) {
				countTypes = crs.getInt(1);		   
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql2="select count(articleId) from articles where typeId= ?";
		//存储数据的数组
		int [] sum=new int[countTypes];
		//遍历得到每个类型的文章数量
			try {
				for (int i = 1; i <= countTypes; i++) {
					CachedRowSet crs2=DBUtil.execQuery(sql2, i);
				     while(crs2.next()) {
					 sum[i-1]=crs2.getInt(1);
				    }
				}
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		
		return sum;
	}
    /**
     * @author 陆亮
     * 查询所有类型名
     */
	@Override
	public List<String> queryTypsName() {
		List<String> types = new ArrayList<String>();
		CachedRowSet crs=DBUtil.execQuery("SELECT typeContent from types ORDER BY typeId");
		try {
			while (crs.next()) {
				String type = crs.getString(1);	
				types.add(type);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return types;
	}
    
	/**
	 * @author 陆亮
	 * 按类型分页查询所有,前台显示
	 */
	@Override
	public PageData queryAllArticlesByTypes(int pageNo, int pageSize, String typesname,String keytitle) {
		String sql="select articleId,articleTitle,userAccount,articleContent,articleDate,typeContent,articleVoice,articleRead,articleStatus,delTime,adminId from articles a inner join types t on a.typeId=t.typeId where typeContent like ? and articleStatus=1 and articleTitle like ? LIMIT ?, ?";
		CachedRowSet crs=DBUtil.execQuery(sql,"%"+typesname+"%","%"+keytitle+"%",(pageNo-1)*pageSize,pageSize);
		Articles articles=null;
		List<Articles> list =new  ArrayList<Articles>();
		try {
			while(crs.next()) {
				   int  articleId = crs.getInt(1);
				   String articleTitle=crs.getString(2);
				   String userAccount=crs.getString(3);
				   String articleContent=crs.getString(4);
				   String articleDate=crs.getString(5);
				   String type=crs.getString(6);
				   String articleVoice=crs.getString(7);
				   int articleRead=crs.getInt(8);
				   int articleStatus=crs.getInt(9);
				   String delDate=crs.getString(10);
				   int adminId=crs.getInt(11);
				   Users users = new Users(userAccount);
				   Types types = new Types(type);
				   articles=  new Articles(articleId, articleTitle, users, articleContent, articleDate,types,articleVoice,articleRead,articleStatus,delDate,adminId);
				list.add(articles);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		String sql2="select count(articleTitle) from articles a inner join types t on a.typeId=t.typeId where typeContent  like ? and articleStatus=1 and articleTitle like ?";
		CachedRowSet crs2=DBUtil.execQuery(sql2, "%"+typesname+"%","%"+keytitle+"%");
		int totalCount=0;
		try {
			while (crs2.next()) {
				totalCount=crs2.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		PageData pageData = new PageData(list,pageNo,pageSize,totalCount);
		return pageData;
	}
    
	/**
	 * @author 陆亮
	 * @param articleId 文章序号
	 * 通过id查询文章跳转到详情页的同时给阅读量加1
	 */
	@Override
	public boolean addRead(int ariticleId) {
		return DBUtil.executeUpdate("update articles set articleRead = articleRead+1 where articleId =?", ariticleId);
	}
     
	/**
	 * @author 陆亮
	 * @return 返回随机语句
	 */
	@Override
	public String queryGoodWord() {
		//先获取总的语句数量,然后求随机数
		int sum=0;
		CachedRowSet crs1=DBUtil.execQuery("select count(wordId) from goodword");
		try {
			while (crs1.next()) {
				sum=crs1.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		//根据语句量取随机整数
		int wordId=new Random().nextInt(sum) + 1; 
		String words="";
		CachedRowSet crs=DBUtil.execQuery("select words from goodword where wordId =?", wordId);
		try {
			while (crs.next()) {
				words=crs.getString(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return words;
	}
}
